package TableAdditions;

import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Dimension;
import java.awt.EventQueue;
import java.awt.List;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseListener;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.ListSelectionModel;
import javax.swing.border.EmptyBorder;
import javax.swing.table.DefaultTableModel;

import Utility.Customer;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import javax.swing.JButton;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import javax.swing.JTable;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

@SuppressWarnings("serial")
public class RouteAdditionMenu extends JFrame {

	private JPanel contentPane;
	Boolean customerFlag=false;
	Boolean productFlag=false;
	Boolean personnelFlag=false;
	Boolean dateFlag=false;
	Boolean dayFlag=false;
	int selectedRow=0;
	String selectedCustomer="",selectedPersonnel="",selectedDate="";
	String selectedCustomerId="";
	String selectedProductId="";
	String selectedPersonelId="";
	String selectedDay="";
	String specialPrice="";
	static int numberOfCustomers=0;
	static Customer[] customers;
	public String routeId="";
	private static int customerCounter=0;
	private JTable customerTable;
	DefaultTableModel model = new DefaultTableModel();
	public static JComboBox customerComboBox;
	private static String selectedValue="";
	
	private static boolean customerComboBoxCalledFromExternalPart = false;
	
	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					RouteAdditionMenu frame = new RouteAdditionMenu();
					frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the frame.
	 */
	public RouteAdditionMenu() {
		
		super("Rut Ekleme Ekranı");
		
		setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
		setBounds(100, 100, 800, 600);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		contentPane.setLayout(null);
		
		
		JComboBox personnelComboBox = new JComboBox();
		personnelComboBox.setBounds(50, 11, 176, 20);
		contentPane.add(personnelComboBox);
		
		System.out.println("Ready to execute sql");
		 	Connection con = null;
	        Statement st = null;
	        ResultSet rs = null;

	        String url = "jdbc:mysql://localhost:3306/mydb";
	        String user = "root";
	        String password = "qweasdzxc";

	        try {
	        	deleteAllRows(model);
	            con = (Connection) DriverManager.getConnection(url, user, password);
	            st = (Statement) con.createStatement();
	            //st.executeUpdate("INSERT INTO job " + "VALUES (1, 'deneme')");
	            //st.executeUpdate("INSERT INTO special_price (special_price_customer_id,special_price_product_id,special_price) " + 
	            	//	"VALUES ('"+cidField.getText()+"'" +",'"+pidField.getText()+"','"+spField.getText()+"')");
	           //st.executeUpdate("ALTER TABLE special_price AUTO_INCREMENT = 1");
	            //st.executeUpdate("DELETE FROM special_price");
	            rs=st.executeQuery("SELECT * from view_personel_ordered_by_name");
	            personnelComboBox.addItem("Personel Adı");
	            
	            
	            while(rs.next())
	            {
	            	personnelComboBox.addItem(rs.getString("personel_name"));
	            }
	            
	            
            	personnelComboBox.addActionListener(new ActionListener() {

					@Override
					public void actionPerformed(ActionEvent event) {
						// TODO Auto-generated method stub
						

	                    JComboBox comboBox = (JComboBox) event.getSource();
	     
	                    //
	                    // Print the selected items and the action command.
	                    //
	                    Object selected = comboBox.getSelectedItem();
	                    System.out.println("Selected Item  = " + selected);
	                    String command = event.getActionCommand();
	                    System.out.println("Action Command = " + command);
	                    
	                    selectedPersonnel=(String) selected;
	                    System.out.println("selectedPersonnel= "+selectedPersonnel);
	                    
	                    if(selectedPersonnel.equals("Personel Adı")){
	                    	personnelFlag=false;
	                    	// TG 11-12-2012 selectedPersonnel combosundan PersonelAdi secili ise asagidaki tablonun icini bosalt
	                    	deleteAllRows(model);
	                    	return;
	                    }
	                    else{
	                    	personnelFlag=true;
	                    	Statement stmt = null;
		                	Connection conn = null;
		                	ResultSet rss = null;
		                	String url = "jdbc:mysql://localhost:3306/mydb";
		        	        String user = "root";
		        	        String password = "qweasdzxc";
		        	        
		        	        try {
								conn = (Connection) DriverManager.getConnection(url, user, password);
								stmt = (Statement) conn.createStatement();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
		    	          
							
							 try {
									rss=stmt.executeQuery("SELECT * from view_personel_ordered_by_name where personel_name='"+selectedPersonnel+"' ");
									while(rss.next()){
										selectedPersonelId=rss.getString("personel_id");
									}
									System.out.println("SelectedPersonel id= "+selectedPersonelId);
									conn.close();
								} catch (SQLException e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
								}  	
	                    }
	                    
	                    if(dateFlag && personnelFlag){
	            			routeId=selectedPersonnel+"/"+selectedDate;
	            			System.out.println("routeId= "+routeId);
	            		}
	                   
	            
	                  
	                    // '"+cidField.getText()+"'
	                    
	                    //
	                    // Detect whether the action command is "comboBoxEdited"
	                    // or "comboBoxChanged"
	                    //
	                    if ("comboBoxEdited".equals(command)) {
	                        //System.out.println("User has typed a string in " +
	                          //      "the combo box.");
	                    } else if ("comboBoxChanged".equals(command)) {
	                        //System.out.println("User has selected an item " +
	                          //      "from the combo box.");
	                    }
	                    
	                    
	                    if(dayFlag && personnelFlag){
	                    	System.out.println("fill table");
	                    	try {
								fillRouteList(customerTable, model, selectedDay, selectedPersonelId, routeId,selectedPersonnel);
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
	                    }
					
					
	                
						
					}
            		
            	});
	            
	            
	            
	            
	            customerComboBox = new JComboBox();
	            customerComboBox.setBounds(50, 77, 138, 20);
	            contentPane.add(customerComboBox);
	            fillCustomerComboBox();
	            
	          
	            customerComboBox.addActionListener(new ActionListener() {
	                public void actionPerformed(ActionEvent event) {
	                    //
	                    // Get the source of the component, which is our combo
	                    // box.
	                    //
	                	Statement stmt = null;
	                	Connection conn = null;
	                	ResultSet rss = null;
	                	String url = "jdbc:mysql://localhost:3306/mydb";
	        	        String user = "root";
	        	        String password = "qweasdzxc";
	        	        
	        	        // TG 11-12-2012 added for check
	        	        if(!customerComboBoxCalledFromExternalPart)
	        	        {
	        	        
		        	        try {
								conn = (Connection) DriverManager.getConnection(url, user, password);
								stmt = (Statement) conn.createStatement();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}  	          
	                	
		                    JComboBox comboBox = (JComboBox) event.getSource();
		     
		                    //
		                    // Print the selected items and the action command.
		                    //
		                    Object selected = comboBox.getSelectedItem();
		                    System.out.println("Selected Item  = " + selected);
		                    String command = event.getActionCommand();
		                    System.out.println("Action Command = " + command);
	                    
		                    selectedCustomer=(String) selected;
		                    try {
		                    	// TG 30-11-2012
		                    	// rss=stmt.executeQuery("SELECT * from customer where customer_name='"+selectedCustomer+"'");
								rss=stmt.executeQuery("SELECT * from customer where customer_name='"+selectedCustomer+"'");
								while(rss.next()){
									selectedCustomerId=rss.getString("customer_id");
								}
								System.out.println("SelectedCustomer id= "+selectedCustomerId);
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
	            
	                    
		                    selectedCustomer=(String) selected;
		                    
		                    try{
		                    if(selected.equals("Müşteri Adı")){
		                    	customerFlag=false;
		                    }
		                    else{
		                    	customerFlag=true;
		                    }
		                    }catch(NullPointerException e){
		                    	
		                    }
		                    // '"+cidField.getText()+"'
		                    
		                    //
		                    // Detect whether the action command is "comboBoxEdited"
		                    // or "comboBoxChanged"
		                    //
		                    if ("comboBoxEdited".equals(command)) {
		                        //System.out.println("User has typed a string in " +
		                          //      "the combo box.");
		                    } else if ("comboBoxChanged".equals(command)) {
		                        //System.out.println("User has selected an item " +
		                          //      "from the combo box.");
		                    }
	        	        }
	                }
					
	            });
	            
	            rs=st.executeQuery("SELECT * from product");
	            int rowCounter=0;
	            while(rs.next()){
	            	rowCounter++;
	            }
	            System.out.println("Number Of Products = "+rowCounter);
	            
	            rs=st.executeQuery("SELECT * from product");
	            
	            JButton btnAdd = new JButton("Ekle");
	            btnAdd.addActionListener(new ActionListener() {
	            	public void actionPerformed(ActionEvent arg0) {
	            	}
	            });
	            
	            
	            btnAdd.addMouseListener(new MouseAdapter() {
	            	@Override
	            	public void mouseClicked(MouseEvent arg0) {
	            		if(dayFlag && customerFlag && personnelFlag){
	            			System.out.println("ready to add route");
	            			
	            			Statement stmt = null;
		                	Connection conn = null;
		                	ResultSet rss = null;
		                	String url = "jdbc:mysql://localhost:3306/mydb";
		        	        String user = "root";
		        	        String password = "qweasdzxc";
		        	        
		        	  
		        	        
		        	        try {
		        	            
								conn = (Connection) DriverManager.getConnection(url, user, password);
								stmt = (Statement) conn.createStatement();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
		    	         
							
		                    try {
		                    	// TG-01-12-2012 asagi tasindi
		                    	deleteAllRows(model);
		                    	
		                    	rss=stmt.executeQuery("SELECT * from  route WHERE route_day='"+selectedDay+"' and route_owner_id='"+selectedPersonelId+"'");
		                    	//stmt.executeUpdate("ALTER TABLE route_destination_list AUTO_INCREMENT = 1");
		        	            //st.executeUpdate("DELETE FROM special_price");
		                    	
		                    	 customerTable.setPreferredScrollableViewportSize(new Dimension(460, 300));
		                    	 customerTable.setFillsViewportHeight(true);
                 	 
		                    	 
		                    	 int rowCounter=0;
		                    	 while(rss.next()){
		                    		 String route_id=rss.getString("route_id");
		                    		 System.out.println("route_id= "+route_id);
		                    		 rowCounter++;
		                    	 }
		                
		                    	 routeId=selectedPersonnel+"/"+selectedDay;
		                    	 
		                    	if(rowCounter==0){
		                    		stmt.executeUpdate("INSERT INTO route (route_id,route_owner_id,route_day) " + "VALUES ('"+routeId+"'" +
		    			            		",'"+selectedPersonelId+"','"+selectedDay+"')");
		                    		stmt.executeUpdate("INSERT INTO route_destination_list (route_id,destination_id) " + "VALUES ('"+routeId+"'" +
		    			            		",'"+selectedCustomerId+"')");	
		                    	}
		                    	else{
		             
		                    		rss=stmt.executeQuery("SELECT * from  route_destination_list WHERE route_id='"+routeId+"'");
		                    		
		                    		// TG 01-12-2012
		                    		// Ayni destination a bu personel tekrar kayit girmemesi saglanmali
		                    		boolean insertedPreviously = false;
			                    	while(rss.next()){
			                    		String destination_id=rss.getString("destination_id");
			                    		if( destination_id.equals(selectedCustomerId) )
			                    		{
			                    			insertedPreviously = true;
			                    			break;
			                    		}			                    		
				                   	}
				                   	
			                    	if( !insertedPreviously )
			                    	{
			                    		stmt.executeUpdate("INSERT INTO route_destination_list (route_id,destination_id) " + "VALUES ('"+routeId+"'" +
			                    				",'"+selectedCustomerId+"')");
			                    	}
		                    	}
		                    	
		                    	rss=stmt.executeQuery("SELECT * from  route_destination_list WHERE route_id='"+routeId+"' ORDER BY destination_id");
                   	
		                    	// TG data arrayList in icine alinip model e eklenecek.
		                    	// Direk eklenmeyecek
		                    	ArrayList<String> arr = new ArrayList<String>();
		                    	
		                    	while(rss.next())
		                    	{
			                   		 Object[][] data = new Object[1][1];
			                   		 
			                   		 for(int n=0;n<customers.length;n++){
			                   			 
			                   			 if(rss.getInt("destination_id")==customers[n].getCustomerId()){
			                   				 data[0][0]=customers[n].getCustomerName();
			                   				arr.add(customers[n].getCustomerName());
			                   			 }			                   			 
			                   		 }
			                       	 System.out.println("destination id = "+rss.getString("destination_id"));		                       	 
			                       	 
			                   		 //model.addRow(data[0]);
		                    	}		
		                    	
		                    	Collections.sort(arr);
		                    	
		                    	Object[][] data = new Object[1][1];
		                    	for( int index = 0; index<arr.size() ; index++ )
		                    	{
		                    		data[0][0]=arr.get(index);
		                    		model.addRow(data[0]);
		                    	}
		                    	
		                    	
		                    	//customerTable.setModel(model);	
		                    	fillCustomerComboBox();
		                    	
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
	                    	
	                    	
	                    	
	                    }
					
					
	                
						
	            			
	            		
	            	}
	            });
	            btnAdd.setBounds(211, 76, 89, 23);
	            contentPane.add(btnAdd);
	            
	            JComboBox dayComboBox = new JComboBox();
	            dayComboBox.setToolTipText("");
	            dayComboBox.setBounds(253, 11, 121, 20);
	            contentPane.add(dayComboBox);
	            
	            dayComboBox.addItem("Gün");
	            
	            JPanel panel = new JPanel();
	            panel.setBounds(68, 147, 430, 401);
	            panel.setBackground(Color.blue);
	            
	            
	            customerTable = new JTable(model);
	            customerTable.setBounds(62, 138, 400, 300);
	           
	            customerTable.setPreferredScrollableViewportSize(new Dimension(420, 300));
	            customerTable.setFillsViewportHeight(true);
	            String header="Müşteri";
	            model.addColumn(header);
	            panel.add(customerTable.getTableHeader());
	           //contentPane.add(customerTable);
	            JScrollPane scrollPane = new JScrollPane(customerTable);
	            customerTable.setFillsViewportHeight(true);
	            panel.setLayout(new BorderLayout());
	            panel.add(customerTable.getTableHeader(),BorderLayout.CENTER);
	            panel.add(scrollPane);
	            //contentPane.add(customerTable, BorderLayout.CENTER);
	            contentPane.add(panel);
	            
	            JButton deleteButton = new JButton("Sil");
	            deleteButton.setBounds(323, 75, 98, 25);
	            contentPane.add(deleteButton);
	            
	            
	            customerTable.addMouseListener(new MouseAdapter() {
					
					
					@Override
					public void mouseClicked(MouseEvent arg0) {
						// TODO Auto-generated method stub
						 if (customerTable.getColumnSelectionAllowed()
				                    && !customerTable.getRowSelectionAllowed()) {
							  
				                System.out.println("11111111111111");
				                // Column selection is enabled
				                // Get the indices of the selected columns
				                int[] vColIndices = customerTable.getSelectedColumns();
				            } else if (!customerTable.getColumnSelectionAllowed()
				                    && customerTable.getRowSelectionAllowed()) {
				            	// System.out.println("222222222222");
				            	 selectedRow=customerTable.getSelectedRow(); 
				            	 selectedValue=(String) customerTable.getValueAt(selectedRow,0); 
				            	 System.out.println("selectedValue= "+selectedValue);
				            	 //System.out.println("selectedCustomer= "+customerNames[selectedRow]);
				                // Row selection is enabled
				                // Get the indices of the selected rows
				                int[] rowIndices = customerTable.getSelectedRows();
				            } else if (customerTable.getCellSelectionEnabled()) {
				                // Individual cell selection is enabled
				            	System.out.println("333333333333");

				                // In SINGLE_SELECTION mode, the selected cell can be retrieved using
				            	customerTable.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
				                int rowIndex = customerTable.getSelectedRow();
				                int colIndex = customerTable.getSelectedColumn();
				              

				                // In the other modes, the set of selected cells can be retrieved using
				                customerTable.setSelectionMode(ListSelectionModel.SINGLE_INTERVAL_SELECTION);
				                customerTable.setSelectionMode(ListSelectionModel.MULTIPLE_INTERVAL_SELECTION);

				                // Get the min and max ranges of selected cells
				                int rowIndexStart = customerTable.getSelectedRow();
				                int rowIndexEnd = customerTable.getSelectionModel().getMaxSelectionIndex();
				                int colIndexStart = customerTable.getSelectedColumn();
				                int colIndexEnd = customerTable.getColumnModel().getSelectionModel()
				                    .getMaxSelectionIndex();

				                // Check each cell in the range
				                for (int r=rowIndexStart; r<=rowIndexEnd; r++) {
				                    for (int c=colIndexStart; c<=colIndexEnd; c++) {
				                        if (customerTable.isCellSelected(r, c)) {
				                        	System.out.println("4444444444444");
				                            // cell is selected
				                        }
				                    }
				                }
				            }
						
					}
				});
		            
	            
	            
	            
	            
	            deleteButton.addMouseListener(new MouseAdapter() {
	            	@Override
	            	public void mouseClicked(MouseEvent arg0) {
	            		if(!(selectedValue.equals("Müşteri Adı") || selectedValue.equals("")) ){
	            			//System.out.println(selectedValue+" will be deleted");
	            			

	            			Statement stmt = null;
		                	Connection conn = null;
		                	ResultSet rss = null;
		                	String url = "jdbc:mysql://localhost:3306/mydb";
		        	        String user = "root";
		        	        String password = "qweasdzxc";
		        	        
		        	  
		        	        
		        	        try {
		        	            
								conn = (Connection) DriverManager.getConnection(url, user, password);
								stmt = (Statement) conn.createStatement();
								int selectedValueId = 0;
								for(int m=0;m<customers.length;m++){
									System.out.println("get customer name= "+customers[m].getCustomerName());
									if(selectedValue.equals(customers[m].getCustomerName())){
										selectedValueId=customers[m].getCustomerId();
										break;
									}
								}
								
								stmt.executeUpdate("DELETE FROM route_destination_list WHERE destination_id='"+selectedValueId+"'");
								fillRouteList(customerTable, model, selectedDay, selectedPersonelId, routeId,selectedPersonnel);
								fillCustomerComboBox();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
		    	         
	            			
	            		}
	            	}
	            	
	            });
	            
	          
	            //JScrollPane scrollPane = new JScrollPane(customerTable);
                //Add the scroll pane to this panel.
                //add(scrollPane);
	            
	            
	            
	            // getting selected item from table
	            
	           
	  
	            
	            
	            String[] daysOfWeek = {"Pazartesi","Sali","Carsamba","Persembe","Cuma","Cumartesi","Pazar"};
	            int c=0;
	            
	            while(c<daysOfWeek.length){
	            	 dayComboBox.addItem(daysOfWeek[c]);
	            	 c++;
	            }
	            
	            dayComboBox.addActionListener(new ActionListener() {

					@Override
					public void actionPerformed(ActionEvent event) {
						// TODO Auto-generated method stub
						
						 JComboBox comboBox = (JComboBox) event.getSource();
					     
		                    //
		                    // Print the selected items and the action command.
		                    //
		                    Object selected = comboBox.getSelectedItem();
		                    System.out.println("Selected Item  = " + selected);
		                    String command = event.getActionCommand();
		                    System.out.println("Action Command = " + command);
		                    selectedDay=(String) selected;
		                    
		                    if(selectedDay.equals("Gün")){
		                    	dayFlag=false;
		                    }
		                    else{
		                    	dayFlag=true;
		                    }
		                    
		                    if(dayFlag && personnelFlag){	
		                    	try {
									fillRouteList(customerTable, model, selectedDay, selectedPersonelId, routeId,selectedPersonnel);
									
								} catch (SQLException e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
								}
		                    }
						
						
					}
	            	
	            });
	            
	           
	            
	            

	        } catch (SQLException ex) {
	            Logger lgr = Logger.getLogger(main.class.getName());
	            lgr.log(Level.SEVERE, ex.getMessage(), ex);

	        } finally {
	            try {
	                if (rs != null) {
	                    rs.close();
	                }
	                if (st != null) {
	                    st.close();
	                }
	                if (con != null) {
	                    con.close();
	                }

	            } catch (SQLException ex) {
	                Logger lgr = Logger.getLogger(main.class.getName());
	                lgr.log(Level.WARNING, ex.getMessage(), ex);
	            }
	        }
		
		
	}
	
	
	private static void fillCustomerComboBox() throws SQLException {
		// TODO Auto-generated method stub
		customerComboBox.removeAllItems();
		customerCounter=0;
		numberOfCustomers=0;
		
		Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        String url = "jdbc:mysql://localhost:3306/mydb";
        String user = "root";
        String password = "qweasdzxc";
        con = (Connection) DriverManager.getConnection(url, user, password);
        st = (Statement) con.createStatement();
		
		
		  rs=st.executeQuery("SELECT * from customer where activity = '1' order by customer_name ");
          customerComboBox.addItem("Müşteri Adı");
          while(rs.next())
          {
          	numberOfCustomers++;
          	customerComboBox.addItem(rs.getString("customer_name"));
          }
          
          if(numberOfCustomers>0){
        	  
	          customers=new Customer[numberOfCustomers];
	          customers[customerCounter]=new Customer();
	          rs.first();
	          customers[customerCounter].setCustomerName(rs.getString("customer_name"));
	          customers[customerCounter].setCustomerId(rs.getInt("customer_id"));
	          customerCounter++;
	          System.out.println("Number of customers= "+numberOfCustomers);
	          
	          while(rs.next())
	          {
	          	//System.out.println("Customer name is added to array");
	        	customers[customerCounter]=new Customer();
	        	customers[customerCounter].setCustomerName(rs.getString("customer_name"));
	        	customers[customerCounter].setCustomerId(rs.getInt("customer_id"));
	          	customerCounter++;
	          }
          
          }
          
          try{
	          for(int i=0;i<customerCounter;i++){
	        	  //	System.out.println("customer= "+customerNames[i]);
	          }
          }catch(NullPointerException e){}
		
	}

	public static void deleteAllRows(final DefaultTableModel model) {
	    for( int i = model.getRowCount() - 1; i >= 0; i-- ) {
	        model.removeRow(i);
	    }
	}
	
	private static void fillRouteList(JTable customerTable,DefaultTableModel model,String selectedDay,String selectedPersonelId,
			String routeId,String selectedPersonnel) throws SQLException{
		
		
		
	 	Statement stmt = null;
    	Connection conn = null;
    	ResultSet rss = null;
    	String url = "jdbc:mysql://localhost:3306/mydb";
        String user = "root";
        String password = "qweasdzxc";
        
        
        try {
			conn = (Connection) DriverManager.getConnection(url, user, password);
			stmt = (Statement) conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
     
		
        try {
        	deleteAllRows(model);
        	rss=stmt.executeQuery("SELECT * from  route WHERE route_day='"+selectedDay+"' and route_owner_id='"+selectedPersonelId+"'");
        	//stmt.executeUpdate("ALTER TABLE route_destination_list AUTO_INCREMENT = 1");
            //st.executeUpdate("DELETE FROM special_price");
        	
        	 customerTable.setPreferredScrollableViewportSize(new Dimension(460, 300));
        	 customerTable.setFillsViewportHeight(true);
        	 
        	
        	 
        	 int rowCounter=0;
        	 while(rss.next()){
        		 String route_id=rss.getString("route_id");
        		 System.out.println("route_id= "+route_id);
        		 rowCounter++;
        	 }
    
        	 routeId=selectedPersonnel+"/"+selectedDay;
        	 
        	if(rowCounter==0){
        		stmt.executeUpdate("INSERT INTO route (route_id,route_owner_id,route_day) " + "VALUES ('"+routeId+"'" +
	            		",'"+selectedPersonelId+"','"+selectedDay+"')");
        	}
        	
        	
        	
        	rss=stmt.executeQuery("SELECT * from  route_destination_list WHERE route_id='"+routeId+"' ORDER BY destination_id");
        	
        	
        	ArrayList<String> arr = new ArrayList<String>();
        	ArrayList<String> destinationList = new ArrayList<String>();
        	Map<String,String> destinationCustomerMap = new HashMap<String, String>();
        	
        	while(rss.next()){
        		 Object[][] data = new Object[1][1];
        		 for(int n=0;n<customers.length;n++){
        			 if(rss.getInt("destination_id")==customers[n].getCustomerId()){
        				 data[0][0]=customers[n].getCustomerName();
        				 arr.add(customers[n].getCustomerName());
        			 }
        		 }
            	 System.out.println("destination id = "+rss.getString("destination_id"));
            	 destinationList.add(rss.getString("destination_id"));
        		 //model.addRow(data[0]);
        	}
        	
        	//customerTable.setModel(model);
        	
        	Collections.sort(arr);
        	
        	Object[][] data = new Object[1][1];
        	for( int index = 0; index<arr.size() ; index++ )
        	{
        		data[0][0]=arr.get(index);
        		model.addRow(data[0]);
        	}
        	
			// TG 11-12-2012 customerCombox ini yeniden doldur.
        	customerComboBoxCalledFromExternalPart = true;
    		customerComboBox.removeAllItems();
    		customerCounter=0;
    		numberOfCustomers=0;
    		
    		// TG 11-12-2012 
            rss = stmt.executeQuery("SELECT * from customer where activity = '1' order by customer_name ");
            customerComboBox.addItem("Müşteri Adı");
            while(rss.next())
            {
            	String customer_ID = rss.getString("customer_id");
            	boolean found = false;
            	for( int k = 0; k<destinationList.size(); k++)
            	{
            		String previouslyAddedCustomerItem = destinationList.get(k);
            		if(customer_ID.equals(previouslyAddedCustomerItem))
            		{
            			found = true;
            			break;
            		}
            	}
            	
            	if(!found)
            	{
                	numberOfCustomers++;
                	String customerName = rss.getString("customer_name");
                  	customerComboBox.addItem(customerName);
                  	destinationCustomerMap.put(customer_ID, customerName);
            	}

            }
              
            if(numberOfCustomers>0){
            	 
            	Iterator iter = destinationCustomerMap.entrySet().iterator();
    	        customers=new Customer[numberOfCustomers];
            	while(iter.hasNext())
            	{
            		Map.Entry mEntry = (Map.Entry)iter.next();
            		String customer_ID = (String) mEntry.getKey();
            		int numeric_ID = Integer.parseInt(customer_ID.trim());
            		String customer_Name = (String) mEntry.getValue();
            		
        	        customers[customerCounter]=new Customer();
        	        customers[customerCounter].setCustomerName(customer_Name);
        	        customers[customerCounter].setCustomerId(numeric_ID);
        	        customerCounter++;         		
            		
            	}

            }
            customerComboBoxCalledFromExternalPart = false;
        	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
		
	}
	
	
	}

